<?php

/**
 * 
 */
class Comments_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function fetchComments($report_id) {
        $sql = "SELECT c.*,u.login as login from comments as c JOIN users as u ON (c.user_id=u.user_id) WHERE c.report_id = '$report_id' ";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function fetchCommentsByPlace($city, $street, $id) { // bez uvedeneho ID -ostatne komenty...
        $sql = "SELECT c.*,u.login as login,r.type_id,r.street_noaccent,r.city_noaccent from comments as c 
		JOIN users as u ON (c.user_id=u.user_id) 
		JOIN reports as r ON (c.report_id = r.report_id) 
		WHERE r.city = '$city' AND r.street='$street' AND r.report_id!='$id'";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function fetchLastCommentByUser($user_id, $report_id) {
        $sql = "SELECT c.*,u.login as login from comments as c JOIN users as u ON (c.user_id=u.user_id) WHERE c.report_id = '$report_id' AND c.user_id='$user_id' order by c.comment_id DESC LIMIT 1";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function insertComment($user_id, $title, $text, $report_id, $date) {
        $sql = "INSERT into comments(user_id,report_id,title,comment_text,added) VALUES ('$user_id','$report_id','$title','$text','$date')";
        $query = $this->db->query($sql);
    }

    public function reportComment($user_id, $comment_id) {

        $date = date('Y-m-d H:i:s');

        $sql = "UPDATE comments SET reported=reported+1 WHERE comment_id='$comment_id'";
        $query = $this->db->query($sql);

        $sql = "INSERT into reported_comments VALUES('','$user_id','$comment_id','$date')";
        $query = $this->db->query($sql);
    }

}

?>